package br.com.datasun.biblioteca.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.com.datasun.biblioteca.dao.factory.ConnectionFactory;
import br.com.datasun.biblioteca.entities.Assunto;
import br.com.datasun.biblioteca.entities.Categoria;
import br.com.datasun.biblioteca.exceptions.DAOException;


public class CategoriaDAO {
	
	public List<Categoria> listarTodasCategorias() throws DAOException{
		List<Categoria> resultado = new ArrayList<Categoria>();
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet   = null;
		StringBuilder sql = new StringBuilder();
		sql.append("select c.id, c.nome, a.id, a.nome from categoria c, assunto a where c.id_assunto = a.id");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				long id = resultSet.getLong(1);
				String nome = resultSet.getString(2);
				long id_assunto = resultSet.getLong(3);
				String nome_assunto = resultSet.getString(4);
				Categoria categoria = new Categoria();
				categoria.setId(id);
				categoria.setNome(nome);
				
				Assunto assunto = new Assunto();
				assunto.setId(id_assunto);
				assunto.setNome(nome_assunto);
				categoria.setAssunto(assunto);
				resultado.add(categoria);
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return resultado;
	}
	
    public boolean existeCategoriaComONome(String nmCategoria, long nmId) throws DAOException{
		
		boolean resultado = false;
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet   = null;
		StringBuilder sql = new StringBuilder();
		sql.append("select c.id, ");
		sql.append("       c.nome ");
		sql.append("  from categoria c ");
		sql.append(" where c.nome like ?");
		sql.append("   and c.id <> ?"); //So Traz Registro se o id for diferente
		try{
			//conecta ao banco de dados
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setString(1, nmCategoria);
			preparedStatement.setLong(2, nmId);
			//executa a consulta
			resultSet = preparedStatement.executeQuery();
			//gera os beans/dtos com o resultado da consulta
			if (resultSet.next()) {
				Long id = resultSet.getLong("id");
				String nome = resultSet.getString("nome");
				resultado = true;
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return resultado;
	}	
	
	public Categoria inserir(Categoria categoria) throws DAOException{
		Connection connection = null;
		Assunto assunto = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet = null;
		StringBuilder sql = new StringBuilder();
		sql.append(" insert into categoria (nome, id_assunto) VALUES(?, ?)");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, categoria.getNome());
			assunto = categoria.getAssunto();
			preparedStatement.setLong(2, assunto.getId());
			preparedStatement.execute();
			resultSet = preparedStatement.getGeneratedKeys();

			if(resultSet.next()){
				Long idGerado = resultSet.getLong(1);
				categoria.setId(idGerado);
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return categoria;
	}	
	
	public boolean atualizarCategoria(Categoria categoria) throws DAOException{
		boolean result = false;
		Assunto assunto = null;
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		StringBuilder sql = new StringBuilder();
		sql.append("update categoria set nome = ?, id_assunto = ? where id = ?");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			assunto = categoria.getAssunto();
			preparedStatement.setString(1, categoria.getNome());
			preparedStatement.setLong(2, assunto.getId());
			preparedStatement.setLong(3, categoria.getId());
			preparedStatement.executeUpdate();
			result = true;
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return result;
	}
	
	public boolean excluirCategoriaPorId(Long idCategoria) throws DAOException{
		boolean result = false;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		StringBuilder sql = new StringBuilder();
		sql.append("delete from categoria ");
		sql.append(" where id= ?");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setLong(1, idCategoria);
			preparedStatement.execute();
			result = true;
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return result;
	}	
}
